#!/bin/sh
#
# OracleASMDBClone.sh: shell script to automate cloning and bring up an Oracle database using backup image copies
#
# usage: OracleASMDBClone.sh <New database sid name> <Oracle Home path> <Disk group Name> <Oracle User> <Grid User>
set -x
nsid=$1
oracleHome=$2
backupLocation=$3
osuser=$4
guser=$5
totalmemory=$6
sgapct=$7

if [ "$#" -lt 5 ]; then
 echo "This script needs 5 input parameter"
 echo "usage: OracleASMDBClone.sh <New database sid name> <Oracle Home path> <Disk group Name> <Oracle User> <Grid User>"
 echo " For example: OracleASMDBClone.sh clonedb /home/oracle/app/oracle/product/11.2.0/dbhome_1 DATA1 oracle grid"
exit 1
fi

#logger for error messages
errMsgFile=$nsid"_errMsg"

scriptsRoot=${LB_ROOT}
if [ -z "$scriptsRoot" ];then
    echo "missing env LB_ROOT"
    exit 1
fi

function apmlogger {
     errmsg="$1"
     if [ ! -z "$errmsg" ]; then
        echo "$errmsg" > $errMsgFile
     fi
}

function apmappend {
     errmsg="$1"
     echo " $errmsg" >> $errMsgFile
}

function geterrmsg {
     ecode=$1
     if [ -z "$ecode" ]; then
        echo "missing error code when retrieving error message"
     fi
     msgrepo=${scriptsRoot}/oracle/apmerrmsg.sh
     msg=`grep $ecode"=" $msgrepo | awk -F'=' '{print $NF}'`
     if [ ! -z "$msg" ]; then
        echo "$msg"
     else
        echo "failed to retrieve error message for $ecode"
     fi
}

function logerrormsg {
     ecode=$1
     appender=$2

     emsg=`geterrmsg $ecode`
     apmlogger "$emsg"
     if [ ! -z "$$appender" ]; then
        apmappend $appender
     fi
     echo `$tdate`$nsid": $emsg $appender"
}
#logger

if [ ! -z "$isrestore" ] && [ "$isrestore" = "true" ]; then
ORACLE_HOME=$oracleHome
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
ORACLE_SID=$nsid"1"
export ORACLE_SID
TNS_ADMIN=$tnsadmindir
export TNS_ADMIN
ORACLE_UNQNAME=$nsid
export ORACLE_UNQNAME

su $osuser -c "$ORACLE_HOME/bin/sqlplus -s / as sysdba" << EOF
set heading off
set feedback off
startup mount;
alter database end backup;
alter database open;
SHUTDOWN IMMEDIATE;
exit;
EOF

if [ $? -ne 0 ]; then
   logerrormsg EM_END_HOT_BACKUP $nsid
   exit 1
fi

su $osuser -c "$ORACLE_HOME/bin/srvctl start database -d $nsid"

if [ $? -ne 0 ]; then
   logerrormsg EM_DB_START $nsid
   exit 1
fi
exit 0
#inband restore
fi

. ${scriptsRoot}/oracle/oracleclone/OracleAppMountComFunc.sh

oratab=/etc/oratab
gridhome=`get_grid_home $oratab`
if [ $? -ne 0 ]; then
   logerrormsg EM_GET_GRID_HOME
   exit 1
fi

if [ ! -d "$gridhome" ]; then
   logerrormsg EM_GRID_HOME $gridhome
   exit 1
fi

asmsid=`grep -v "^[#]" $oratab |grep -i +asm | cut -d":" -f1`
export ORACLE_BASE=$ASM_BASE
export ORACLE_HOME=$gridhome
export ORACLE_SID=$asmsid
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

psid=`get_asm_sid $backupLocation $guser`
remountflag=$isremount

echo "******************* remount flag: $remountflag ********************"
echo "******************* psid: $psid ********************"
if [ "$remountflag" = "true" ]; then
#if [ $? -eq 0 ] && [ ! -z "$ischild" ] && [ "$ischild" = "false" ]; then
#remount of existing appaware mounted diskgroup
${scriptsRoot}/oracle/asmclone/OracleASMDBRemount.sh $nsid $oracleHome $backupLocation $osuser
   if [ $? -ne 0 ]; then
      logerrormsg EM_REMOUNT $nsid
      exit 1
   fi
else
#regular appAware mount
BASEDIR=$(dirname $0)
echo $BASEDIR
cd $BASEDIR
# correctly set the ORACLE_BASE AND ORACLE_HOME  for your environment
export ORACLE_BASE=
export ORACLE_HOME=$oracleHome
#ORACLE_BASE=`echo $ORACLE_HOME | awk -F"/product" '{print $(NF-1)}'`
#export ORACLE_BASE=$ORACLE_BASE
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

change_own $osuser $BASEDIR

if [ ! -d $ORACLE_HOME ]; then
   logerrormsg EM_ORACLE_HOME
   exit 1
fi

#if [ ! -d $ORACLE_BASE ]; then
#   echo "could not find ORACLE BASE"
#   exit 1
#fi

backupLocation=`echo $backupLocation | tr '[a-z]' '[A-Z]'`
if [ ! -d $backupLocation ]; then
   backupLocation=`echo $backupLocation | tr '[A-Z]' '[a-z]'`
   if [ ! -d $backupLocation ]; then
        logerrormsg EM_FIND_BACKUP_LOCATION $backupLocation
        exit 1
   fi
fi
dataDir=$backupLocation

# get the backup parameter file location
mname=$backupLocation
echo $mname
pfileloc=/opt/howlink/linkBackup/touch$mname

# get the production sid name from mount
getpname=`ls -ltr $pfileloc | grep '__backup.ora'`
echo $getpname
pname=`echo $getpname | awk '{print $NF}'`
echo $pname
osid=`echo $pname | awk -F"__" '{print $(NF-1)}'`
echo "***************"
echo $osid

if [ -z "$osid" ]; then
   logerrormsg EM_FIND_ORIG_SID
   exit 1
fi 

dbfsPath=$backupLocation/$osid"_datafiles_readonly_catalog.conf"
echo "read only tablespace file path : $dbfsPath"


origfile=$pfileloc/$osid"__backup.ora"
cp $origfile $pfileloc/$osid"__cpfile.ora"
chmod 775 $pfileloc/$osid"__cpfile.ora"
orafile=$pfileloc/$osid"__cpfile.ora"
orarollbacktemp=$pfileloc/$osid"__cpfilerollback.ora"
temporafile=$pfileloc/$osid"__backup_temp.ora"
echo $orafile

openDatabase="openDatabase_"$nsid".sql"
openFullDatabase="openFullDatabase_"$nsid".sql"
openDatabase1="openDatabase1_"$nsid".sql"
openTmpDatabase="openTmpDatabase_"$nsid".sql"
openTmpDatabase1="openTmpDatabase1_"$nsid".sql"
recoverDatabase="recoverDatabase_"$nsid".sql"
verifyDatabase="verifyDatabase"_$nsid".sql"
CLONE_CRT_SQL="Clone_createCtrlFileASM_"$nsid".sql"
addUnnamed="addUnnamed_"$nsid".sql"
tabfilelist="tabfilelist_"$nsid
undofilelist="undofilelist_"$nsid".sql"
pglist="pglist_"$nsid
createRedolog="createRedolog_"$nsid".sql"

sqlplus=$ORACLE_HOME/bin/sqlplus

var="+ASM"
for ASMSID in $var; do
ASMS=`grep -v "^[#]" /etc/oratab | grep -i "$ASMSID" | head -n1 | cut -f1 -d":"`
 echo "asmsid is: $ASMS"
ASMHOME=`grep -v "^[#]" /etc/oratab | grep -i "$ASMS:"  | head -n1 | cut -f2 -d":"`
 echo "asmhome is: $ASMHOME"
done

export ORACLE_BASE=$ASM_BASE
export ORACLE_HOME=$ASMHOME
export LD_LIBRARY_PATH=$ASMHOME/lib:/usr/lib:/lib
export PATH=$ASMHOME/bin:$PATH
export ORACLE_SID="$ASMS"
asmcmd=$ASMHOME/bin/asmcmd

#set appware generation
if [ "$ischild" = "true" ]; then
   pgen=`get_apm_latest_gen $backupLocation $guser`
   gen=$(( pgen + 1 ))
else
   gen=0
fi

apmtag="apmgen_"$gen"_f"
#

su $guser -c $asmcmd << EOF
cd $mname
mkdir $nsid
cd $nsid
mkdir datafile
mkdir archivelog
mkdir controlfile
mkdir onlinelog
mkdir $apmtag
EOF

#scname=`su $guser srvctl config scan | head -n1 | cut -f2 -d":" | cut -f1 -d","`
scname=`su $guser srvctl config scan | head -n1 | awk -F " " '{print $3}' |awk -F "," '{print $1}'`
pnum=`su $guser srvctl config scan_listener | grep -i "TCP:" | head -n1 | cut -f3 -d ":"`

pnum=`echo $pnum | cut -f1 -d ","`

#scname=$(tr -d ' ' <<< "$scname")
#scname=`srvctl config scan | head -n1 | cut -f2 -d":" | cut -f1 -d","`
#pnum=`srvctl config scan_listener | grep -i "TCP:" | head -n1 | cut -f3 -d ":"`

if [ -z $nsid ]; then
   logerrormsg EM_NEW_SID
   exit 1
fi

if [ -z $orafile ]; then
   logerrormsg EM_INIT_FILE
   exit 1
fi

#oobase=$ORACLE_BASE
#echo "Oracle base is $ORACLE_BASE"

thcnt=`grep -i ".thread" $orafile | wc -l`
undocnt=`grep -i ".undo_tablespace" $orafile | wc -l`

# replace SID with new SID
sed "s/'$osid'/'$nsid'/g" $orafile > $temporafile
chmod 755 $temporafile
mv $temporafile $orafile

export ORACLE_BASE=
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

#make required directory structures
if [ -z "$diagnostic_dest" ]; then
  su $osuser -c "mkdir -p $ORACLE_HOME/../../admin/$nsid/adump"
  su $osuser -c "mkdir -p $ORACLE_HOME/../../diag"
else
  su $osuser -c "mkdir -p $diagnostic_dest/$nsid"
  su $osuser -c "mkdir -p $diagnostic_dest/$nsid/admin/adump"
fi

if [[ x$scname = "x" || x$pnum = "x" ]]; then
sed '/remote_listener/d' $orafile > $temporafile
mv $temporafile $orafile
else
remotelistener="*.remote_listener='$scname:$pnum'"
sed '/remote_listener/d' $orafile > $temporafile
mv $temporafile $orafile
echo "$remotelistener" >> $orafile
fi

#ctlfile=`su $guser $asmcmd ls +$dataDir/$SID/controlfile`
dataDir1="+"$dataDir
controlfile="*.control_files='$dataDir1'"
dbcreatefile="*.db_create_file_dest='$dataDir1'"
dbrecoveryfile="*.db_recovery_file_dest='$dataDir1'"
#logarchivedest="*.log_archive_dest_1='LOCATION=$dataDir/$nsid/archivelog/'"

nsidlen=`echo -n $nsid | wc -m`
if [ $nsidlen -gt 8 ]; then
newdb_name=`echo $nsid | cut -c1-8`
db_unique_name=$nsid
else
newdb_name=$nsid
fi

dbname="*.db_name='$newdb_name'"
dbuniquename="*.db_unique_name='$nsid'"

jqprocess="*.job_queue_processes=0"
clustdb="*.cluster_database=false"
tmpundo="*.temp_undo_enabled=false"

#remotelistener="*.remote_listener='$scname:$pnum'"
if [ -z "$totalmemory" ]; then
    #sgatarget="*.sga_target=800m"
    #pgatarget="*.pga_aggregate_target=400m"
    echo " memory size is not defined "
    echo "source memory setup will be used"
else
    if [ ! -z "$totalmemory" ] && [ -z "$sgapct" ]; then
        echo " SGA is not defined "
        echo "memory_target setup will be used"
        sed '/sga_max_size=/d' $orafile > $temporafile
	mv $temporafile $orafile
        sed '/sga_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/pga_aggregate_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/streams_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_max_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_keep_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/large_pool_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        totalmemoryM=$totalmemory"m"
        mtarget="*.memory_target=$totalmemoryM"
        echo "$mtarget" >> $orafile
    else
        echo " total memory and SGA is defined "
        echo "sga and pga setup will be used"
        sed '/sga_max_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/sga_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/pga_aggregate_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/memory_max_target=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/streams_pool_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/db_keep_cache_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
        mv $temporafile $orafile
        sed '/large_pool_size/d' $orafile > $temporafile
        mv $temporafile $orafile
        sgc=$(($totalmemory*$sgapct/100))
        sgam=$sgc"m"
        sgatarget="*.sga_target=$sgam"
        pgc=$(($totalmemory - $sgc ))
        pgam=$pgc"m"
        pgatarget="*.pga_aggregate_target=$pgam"
        echo "$sgatarget" >> $orafile
        echo "$pgatarget" >> $orafile

        lps=$((($sgc*10)/100))
        if [ "$lps" -gt "200" ]; then
           lapsm=$lps"m"
        else
           lapsm=200m
        fi

        largepool="*.large_pool_size=$lapsm"
        echo "$largepool" >> $orafile

        if [ ! -z "$shared_pool_size" ]; then
            sps=$shared_pool_size"m"
            sharedpoolsize="*.shared_pool_size=$sps"
            echo "$sharedpoolsize" >> $orafile
        fi

        if [ ! -z "$db_cache_size" ]; then
           dcs=$db_cache_size"m"
           dbcachesize="*.db_cache_size=$dcs"
           echo "$dbcachesize" >> $orafile
        fi
    fi
 fi

sed '/db_recovery_file_dest_size=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ ! -z "$db_recovery_file_dest_size" ]; then
drfds=$db_recovery_file_dest_size"m"
dbrecoveryfilesize="*.db_recovery_file_dest_size=$drfds"
else
dbrecoveryfilesize="*.db_recovery_file_dest_size=50g"
fi

sed '/processes=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ ! -z "$processes" ]; then
process="*.processes=$processes"
else
process="*.processes=500"
fi

if [ ! -z "$open_cursors" ]; then
sed '/open_cursors=/d' $orafile > $temporafile
mv $temporafile $orafile
opencursors="*.open_cursors=$open_cursors"
echo "$opencursors" >> $orafile
fi

sed '/oracle_base=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/inmemory_/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/use_large_pages=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/control_files=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/audit_file_dest=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_create_file_dest=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_recovery_file_dest=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/diagnostic_dest=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_dest_1=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/cluster_database=/d' $orafile > $temporafile
mv $temporafile $orafile
#sed '/remote_listener/d' $orafile > $temporafile
sed '/local_listener/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/user_dump_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/core_dump_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_name/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/dispatchers=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/SPFILE=/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/thread=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/THREAD=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/instance_number=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/undo_tablespace=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/__/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/service_names=/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_create_online_log_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/SPFILE=/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/dg_broker_config_file/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/dg_broker_start/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/fal_client/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/fal_server/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_config/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_trace/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_min_succeed_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_file_name_convert/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/standby_file_management/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_file_name_convert/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/archive_lag_target/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/log_archive_trace/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/cluster_interconnects/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/_diag_adr_trace_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/cluster_database_instances/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/instance_name/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/db_unique_name/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/standby_archive_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/background_dump_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/_fix_control/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/event/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/enable_goldengate_replication/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/_adg_parselock_timeout/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/parallel_min_servers/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/parallel_max_servers/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/parallel_servers_target/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/use_large_pages/d' $orafile > $temporafile
mv $temporafile $orafile

chmod 755 $orafile

echo "$controlfile" >> $orafile
echo "$dbname" >> $orafile
echo "$dbuniquename" >> $orafile
#echo "$auditfile" >> $orafile
echo "$dbcreatefile" >> $orafile
echo "$dbrecoveryfile" >> $orafile
echo "$dbrecoveryfilesize" >> $orafile
if [ ! -z "$diagnostic_dest" ]; then
 dbdiagnostic="*.diagnostic_dest='$diagnostic_dest'"
 echo "$dbdiagnostic" >> $orafile
 adest="*.audit_file_dest='$diagnostic_dest/$nsid/admin/adump'"
 echo "$adest" >> $orafile
fi
#echo "$logarchivedest" >> $orafile
echo "$process" >> $orafile
echo "$clustdb" >> $orafile
#echo "$remotelistener" >> $orafile
echo "$jqprocess" >> $orafile
tmpundocnt=0
tmpundocnt=`grep ".temp_undo_enabled" $orafile | wc -l`
if [ "$tmpundocnt" -gt "0" ]; then
 sed '/temp_undo_enabled/d' $orafile > $temporafile
 mv $temporafile $orafile
 echo "$tmpundo" >> $orafile
fi

sed '/disk_asynch_io/d' $orafile > $temporafile
mv $temporafile $orafile
diskasyncio="*.disk_asynch_io='TRUE'"
echo "$diskasyncio" >> $orafile

chmod 755 $orafile

ncount=`grep "ASM_" asm_node_config.conf | cut -f2 -d":" | wc -l`

var=`grep "ASM_" $BASEDIR/asm_node_config.conf | cut -f2 -d":"`
m=0
for x in $var; do
  m=$(($m + 1));
  norasid1=$nsid$m
	cthread="$norasid1.thread=$m"
	cinst="$norasid1.instance_number=$m"
	cundo="$norasid1.undo_tablespace='UNDOTBS_CL$m'"
	echo "$cthread" >> $orafile
	echo "$cinst" >> $orafile
	echo "$cundo" >> $orafile
done

# generate a controlfile creation script
./generateASMCtrlFileScript.sh $nsid $dataDir $osid $guser

retval=$?
if [ $retval -gt 0 ]; then
   logerrormsg EM_GENERATE_CONTROLFILE
   exit 1;
fi

#create sql file to open the database and add tempfile
if [ -e $openDatabase ]; then
   #echo "removing $openDatabase before generating a new one"
   rm -f $openDatabase
fi

if [ -e $openDatabase1 ]; then
  # echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi

if [ -e $recoverDatabase ]; then
   #echo "removing $recoverDatabase before generating a new one"
   rm -f $recoverDatabase
fi

if [ -e $addUnnamed ]; then
   #echo "removing $openDatabase before generating a new one"
   rm -f $addUnnamed
fi

if [ -e $openFullDatabase ]; then
   #echo "removing $openFullDatabase before generating a new one"
   rm -f $openFullDatabase
fi

if [ -e $tabfilelist ]; then
   #echo "removing $tabfilelist before generating a new one"
   rm -f $tabfilelist
fi

if [ -e $undofilelist ]; then
  # echo "removing $undofilelist "
   rm -f $undofilelist
fi

if [ -e $orarollbacktemp ]; then
  # echo "removing $orarollbacktemp "
   rm -f $orarollbacktemp
fi

if [ -e $createRedolog ]; then
  # echo "removing $createRedolog "
   rm -f $createRedolog
fi

# now export new SID, do this outside of shell, before invoke this shell
export ORACLE_SID=$nsid
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
# copy ora file
initf1=$ORACLE_HOME/dbs/"init"$nsid"1.ora"
initf=$ORACLE_HOME/dbs/"init"$nsid".ora"

chmod 775 $orafile
# keep a copy of ora file under ORACLE_BASE
cp $orafile $ORACLE_HOME/../../"init"$nsid".ora"
#check if spfile<sid>.ora exist, and move it if true
spf_fs=$ORACLE_HOME/dbs/"spfile"$nsid".ora"
if [ -f $spf_fs ]; then
   mvts=`date +%Y%m%d`
   su $osuser -c "mv $spf_fs $ORACLE_HOME/dbs/spfile$nsid.ora-$mvts"
fi
# start the database for cloning
spf=$dataDir1/$nsid/"spfile"$nsid".ora"
su $osuser -c "$sqlplus / as sysdba @createPfile.sql $orafile $spf "

su $osuser -c "echo 'SPFILE=$spf' > $initf1"
su $osuser -c "echo 'SPFILE=$spf' > $initf"

su $osuser -c "srvctl add database -d $nsid -o $ORACLE_HOME -p '$spf'"
change_own $osuser $CLONE_CRT_SQL
ORACLE_UNQNAME=$nsid
export ORACLE_UNQNAME
su $osuser -c "sqlplus / as sysdba @mountDatabase.sql $CLONE_CRT_SQL"

retval=$?
if [ $retval -gt 0 ]; then
   logerrormsg EM_MOUNT_DATABASE
   exit 1
fi

export ORACLE_SID=$nsid
opencnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'MOUNTED';
exit;
EOF1`
retval=$?

if [ $retval -gt 0 ]; then
 logerrormsg EM_DB_MOUNT
 exit 1;
elif [ $opencnt -eq 0 ]; then
 logerrormsg EM_DB_MOUNT
 exit 1;
fi

if [ -e $CLONE_CRT_SQL ]; then
   echo $CLONE_CRT_SQL
   rm -f $CLONE_CRT_SQL
fi

export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=$nsid
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

dbrecover="dbrecover_"$nsid".txt"
export ORACLE_SID=$nsid
# catpath=$dataDir1"/"$osid
catpath=$dataDir1
nsid1=$nsid"1"
diffcnt=$(($ncount - $undocnt))


vnum=0
vnum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '12%';
exit;
EOF1`
echo "********************* vnum=$vnum ***********"
pdbcnt=0
if [ "$vnum" -gt "0" ]; then
  pdbcnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
  set head off;
  set feedback off
  select count(*) from v\$pdbs where name like 'PDB%SEED';
  exit;
EOF1`
fi
export vnum
export pdbcnt

echo "******* recovering database *********** "
echo " recovering database " > $dbrecover
change_own $osuser $dbrecover

#if [ $pdbcnt -gt 0 ]; then
su $osuser -c "$sqlplus / as sysdba " << EOF
alter database archivelog;
exit;
EOF
#fi

ORACLE_UNQNAME=$nsid
export ORACLE_UNQNAME
./recoverRman.sh $catpath $nsid $osuser $ORACLE_HOME $dbfsPath

retval=0
recoverdb=true
if [ -z "$rrecovery" ] || [ "$rrecovery" = "false" ]; then
   recoverdb=false
fi

if [ "$recoverdb" = "true" ]; then
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openDatabase
echo "alter database open resetlogs;" >> $openDatabase

echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$dataDir1/$nsid/temp_cl01.dbf' size 5g autoextend on;" >> $openDatabase
echo "exit" >> $openDatabase

echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openFullDatabase
echo "alter database open;" >> $openFullDatabase

echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$dataDir1/$nsid/temp_cl01.dbf' size 5g autoextend on maxsize unlimited;" >> $openFullDatabase
echo "exit" >> $openFullDatabase

change_own $osuser $openDatabase
change_own $osuser $openFullDatabase

openDBlog="openDBlog_"$nsid".txt"

if [ -e $openDBlog ]; then
#   echo "removing $openDBlog before generating a new one"
   rm -f $openDBlog
fi

echo "Opening Database is started..." > $openDBlog

change_own $osuser $openDBlog

if [ "$pdbcnt" -gt "0" ]; then
su $osuser -c "$sqlplus / as sysdba @recoverPDBSeed.sql"
fi

if [ ! -z "$redosize" ]; then
rsize=$redosize"M"
else
rsize=1000"M"
fi

dthcnt=`su $osuser -c "sqlplus -S / as sysdba " << EOF
set head off;
select max(thread#) from v\\$log;
exit;
EOF`
echo "********thread count $dthcnt*****"

dgpcnt=`su $osuser -c "sqlplus -S / as sysdba " << EOF
set head off;
select max(group#) from v\\$log;
exit;
EOF`
echo "********group count $dgpcnt******"

undocnta=`grep -i ".undo_tablespace" $orafile | wc -l`
thrcnta=`grep -i ".thread" $orafile | wc -l`

n=1
dthcnt=1
while [ "$n" -lt "$thrcnta" ]; do
        dthcnt=$(($dthcnt + 1))
        dgpcnt=$(($dgpcnt + 1))
        echo "alter database add logfile thread $dthcnt group $dgpcnt ('$dataDir1') size $rsize reuse;" > $createRedolog
        dgpcnt=$(($dgpcnt + 1))
        echo "alter database add logfile thread $dthcnt group $dgpcnt ('$dataDir1') size $rsize reuse;" >> $createRedolog
        n=$(($n + 1));
done

if [ -f "$createRedolog" ]; then
   echo "exit" >> $createRedolog
   change_own $osuser $createRedolog

export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$createRedolog " >> $openDBlog
retval1=$?
fi

nsid1=$nsid"1"
export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openDatabase " >> $openDBlog
retval1=$?

orabug=`grep ORA-01092 $openDBlog`
if [ -z "$orabug" ]; then
orabug=`grep ORA-03113 $openDBlog`
fi

if [ ! -z "$orabug" ]; then
sleep 60
su $osuser -c "$ORACLE_HOME/bin/rman target / log $dbrecover append " << EOF
run {
startup mount;
recover database;
}
exit;
EOF
su $osuser -c "sqlplus / as sysdba @$openDatabase " >> $openDBlog
oraopen=`grep ORA-01139 $openDBlog`
if [ ! -z "$oraopen" ]; then
su $osuser -c "sqlplus / as sysdba @$openFullDatabase " >> $openDBlog
fi
retval1=$?
fi

if [ "$retval1" -gt "0" ]
then
 while [ "$retval1" -gt "0" ]; do

undfcnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
select count(*) from v\$datafile where name like '%/UNNAMED%';
exit;
EOF1`

  if [ "$undfcnt" -gt "0" ]; then

      undfnum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF2'
        set head off;
        select file# from v\$datafile where name like '%/UNNAMED%';
        exit;
EOF2`

        echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $addUnnamed
        echo "alter database create datafile $undfnum as new;" >> $addUnnamed
        echo "exit" >> $addUnnamed
        change_own $osuser $addUnnamed

        su $osuser -c "sqlplus / as sysdba @$addUnnamed "
        ./recoverRman.sh $catpath $nsid $osuser $ORACLE_HOME $dbfsPath
        su $osuser -c "sqlplus / as sysdba @$openDatabase " >> $openDBlog
   fi
retval1=$?;
 done
fi

oraissue=`grep ORA-01196 $openDBlog`
if [ ! -z "$oraissue" ]; then
  #exit $oraissue
  logerrormsg EM_DB_OPEN "$oraissue"
  exit 1
fi
ggusername=`su $osuser -c "$sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none;
select distinct owner from dba_segments where segment_name like 'GGS_%' and owner<>'SYS' and owner<>'SYSTEM' and rownum<2;
exit;
EOF1`

if [ ! -z "$ggusername" ]; then
echo "Golden Gate User:$ggusername"
su $osuser -c "sqlplus -S / as sysdba " <<EOF1
WHENEVER SQLERROR EXIT SQL.SQLCODE
alter user "$ggusername" account lock;
startup force restrict;
drop user "$ggusername" cascade;
exit;
EOF1
retval=$?
echo "Drop User: $retval"

if [ $retval -gt 0 ]; then
su $osuser -c "sqlplus -S / as sysdba " <<EOF1
WHENEVER SQLERROR EXIT SQL.SQLCODE
shutdown abort;
startup force restrict;
drop user "$ggusername" cascade;
exit;
EOF1
fi
fi


opencnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?
if [ $retval -gt 0 ]; then
su $osuser -c "$sqlplus / as sysdba " << EOF
startup force restrict;
exit;
EOF
fi

# drop old undo tablespace

ponum=0
ponum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status = 'NEEDS RECOVERY';
exit;
EOF1`

su $osuser -c "sqlplus -S / as sysdba " << EOF > $undofilelist
set head off;
set feed off;
set echo off;
set newpage none;
select distinct tablespace_name from dba_undo_extents;
exit;
EOF

if [ "$ponum" -gt "0" ]; then
undorollofflist=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select '*._offline_rollback_segments='||ltrim(rtrim(listagg(to_char(SEGMENT_NAME),',') WITHIN GROUP (ORDER BY SEGMENT_NAME ))) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status = 'NEEDS RECOVERY';
exit;
EOF1`
fi

echo "create undo tablespace UNDOTBS_DEF datafile '$dataDir1' size 500m autoextend on;" > $openTmpDatabase
echo "alter system set undo_tablespace=UNDOTBS_DEF scope=both;" >> $openTmpDatabase
echo "exit" >> $openTmpDatabase

spf=$dataDir1/$nsid/"spfile"$nsid".ora"
su $osuser -c "$sqlplus / as sysdba @createTmpPfile.sql $spf $orarollbacktemp "
chmod 755 $orarollbacktemp
if [ "$ponum" -gt "0" ]; then
echo $undorollofflist >> $orarollbacktemp
fi
ttbs="*.undo_tablespace=UNDOTBS_DEF"
sed '/undo_tablespace/d' $orarollbacktemp > $temporafile
mv $temporafile $orarollbacktemp
echo $ttbs >> $orarollbacktemp
chmod 755 $orarollbacktemp

chmod 755 $openTmpDatabase
export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openTmpDatabase " >> $openDBlog


echo "shutdown immediate;" > $openTmpDatabase1
echo "startup force restrict pfile='$orarollbacktemp'" >> $openTmpDatabase1
echo "alter system set undo_tablespace=UNDOTBS_DEF;" >> $openTmpDatabase1

while read line
do
    set -- $line
    undoname=$1
        echo "outside if $undoname"
        if [ "x$undoname" != "x" ]; then
        echo "inside if $undoname"
        echo "drop tablespace $undoname including contents and datafiles;" >> $openTmpDatabase1
        fi
done < $undofilelist
echo "shutdown immediate;" >> $openTmpDatabase1
echo "startup force;" >> $openTmpDatabase1
echo "exit" >> $openTmpDatabase1

chmod 755 $openTmpDatabase1

echo "******************" >> $openDBlog
echo "ACT_PDB_TF" >> $openDBlog

export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openTmpDatabase1 " >> $openDBlog
retval=$?

oraissue=`awk '/ACT_PDB_TF/{y=1;next}y' $openDBlog | grep ORA-03113`
if [ -z "$oraissue" ]; then
oraissue=`awk '/ACT_PDB_TF/{y=1;next}y' $openDBlog | grep ORA-01548`
fi
if [ ! -z "$oraissue" ]; then
sleep 60
echo "startup force restrict;" > $openTmpDatabase
echo "exit" >> $openTmpDatabase

chmod 755 $openTmpDatabase

export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openTmpDatabase " >> $openDBlog
ponum=0
ponum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status = 'NEEDS RECOVERY';
exit;
EOF1`

if [ "$ponum" -gt "0" ]; then
undorollofflist=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select '*._offline_rollback_segments='||ltrim(rtrim(listagg(to_char(SEGMENT_NAME),',') WITHIN GROUP (ORDER BY SEGMENT_NAME ))) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status= 'NEEDS RECOVERY';
exit;
EOF1`
fi

sed '/offline_rollback_segments/d' $orarollbacktemp > $temporafile
mv $temporafile $orarollbacktemp
if [ "$ponum" -gt "0" ]; then
echo $undorollofflist >> $orarollbacktemp
fi

ttbs="*.undo_tablespace=UNDOTBS_DEF"
sed  '/undo_tablespace/d' $orarollbacktemp  > $temporafile
mv $temporafile $orarollbacktemp
echo  $ttbs >> $orarollbacktemp
chmod 755 $orarollbacktemp

echo "shutdown immediate" > $openTmpDatabase1
echo "startup force restrict pfile='$orarollbacktemp'" >> $openTmpDatabase1
echo "alter system set undo_tablespace=UNDOTBS_DEF;" >> $openTmpDatabase1

while read line
do
    set -- $line
    undoname=$1
        echo "outside if $undoname"
        if [ "x$undoname" != "x" ]; then
        echo "inside if $undoname"
        echo "drop tablespace $undoname including contents and datafiles;" >> $openTmpDatabase1
        fi
done < $undofilelist

echo "shutdown immediate;" >> $openTmpDatabase1
echo "startup force;" >> $openTmpDatabase1
echo "exit" >> $openTmpDatabase1

chmod 755 $openTmpDatabase1
sleep 60
export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openTmpDatabase1 " >> $openDBlog
orasmon=`grep ORA-01154 $openDBlog`
if [ ! -z "$orasmon" ]; then
 sed -e '1,1 s/shutdown immediate/shutdown abort/g' $openTmpDatabase1 > $temporafile
 mv $temporafile $openTmpDatabase1
 su  $osuser -c "sqlplus / as sysdba @$openTmpDatabase1 " >> $openDBlog
fi
fi

if [ "$pdbcnt" -gt "0" ]; then
su $osuser -c "$ORACLE_HOME/bin/rman target / log $dbrecover append " << 'EOF'
run {
ALTER pluggable database all open;
alter session set "_oracle_script"=true;
shutdown immediate;
startup;
ALTER pluggable database all open;
alter pluggable database all save state;
}
exit;
EOF
fi

opencnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ $retval -gt 0 ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
 exit 1;
elif [ $opencnt -eq 0 ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
 exit 1;
fi

tempg=0
tempg=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from dba_tablespace_groups;
exit;
EOF1`

if [ "$tempg" -gt "0" ]; then
tempgn=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feed off;
set echo off;
select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE');
exit;
EOF1`
fi

tabgroup="tabgroup_"$nsid".txt"

if [ "x$tempgn" != "x" ]; then
su $osuser -c "sqlplus -S / as sysdba " << 'EOF1' > $tabgroup
set head off;
set feed off;
set echo off;
select tablespace_name from dba_tablespace_groups where group_name in (select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'));
exit;
EOF1
chmod 755 $tabgroup
else
tempn=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
select property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
exit;
EOF1`
fi

tempn=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
select property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
exit;
EOF1`

if [ "$pdbcnt" -eq "0" ]; then
su $osuser -c "sqlplus -S / as sysdba " << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and ts# not in (select ts# from v\$datafile);
exit;
EOF
else
su $osuser -c "sqlplus -S / as sysdba " << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED') and ts# not in (select ts# from v\$datafile where con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED'));
exit;
EOF
fi

dthcnt=`su $osuser -c "sqlplus -S / as sysdba " << EOF
set head off;
select max(thread#) from v\\$log;
exit;
EOF`
#echo "th count $dthcnt"
dgpcnt=`su $osuser -c "sqlplus -S / as sysdba " << EOF
set head off;
select max(group#) from v\\$log;
exit;
EOF`
#echo "gp count $dgpcnt"

undocnta=`grep -i ".undo_tablespace" $orafile | wc -l`
thrcnta=`grep -i ".thread" $orafile | wc -l`

#echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openDatabase1

echo "alter database default temporary tablespace TEMP_CL;" > $openDatabase1

while read line
do
    set -- $line
    tabname=$1
        echo "outside if $tabname"
        if [ "x$tabname" != "x" ]; then
        echo "inside if $tabname"
        tmpstr=" '$dataDir1' size 5g autoextend on;"
        echo "drop tablespace $tabname including contents and datafiles;" >> $openDatabase1
        echo "create temporary tablespace $tabname tempfile $tmpstr" >> $openDatabase1
        fi
done < $tabfilelist


echo "shutdown immediate;" >> $openDatabase1
echo "startup;" >> $openDatabase1


if [ "x$tempgn" != "x" ]; then
while read line
do
    set -- $line
    tabgname=$1
        echo "tab group if $tabgname"
        if [ "x$tabgname" != "x" ]; then
        echo "in tab group if $tabgname"
        echo "alter tablespace $tabgname tablespace group $tempgn;" >> $openDatabase1
        fi
done < $tabgroup

echo "alter database default temporary tablespace $tempgn;" >> $openDatabase1
else
echo "alter database default temporary tablespace $tempn;" >> $openDatabase1
fi

n=1
dthcnt=1
while [ "$n" -lt "$thrcnta" ]; do
	dthcnt=$(($dthcnt + 1))
	dgpcnt=$(($dgpcnt + 1))
	echo "alter database add logfile thread $dthcnt group $dgpcnt ('$dataDir1') size $rsize reuse;" >> $openDatabase1
	dgpcnt=$(($dgpcnt + 1))
	echo "alter database add logfile thread $dthcnt group $dgpcnt ('$dataDir1') size $rsize reuse;" >> $openDatabase1
	echo "alter database enable thread $dthcnt;" >> $openDatabase1
        n=$(($n + 1)); 
done

export ORACLE_BASE=$ASM_BASE
export ORACLE_HOME=$ASMHOME
export LD_LIBRARY_PATH=$ASMHOME/lib:/usr/lib:/lib
export PATH=$ASMHOME/bin:$PATH
export ORACLE_SID="$ASMS"

#reprocess for original SID, to support gc
. ${scriptsRoot}/oracle/oracleclone/OracleAppMountComFunc.sh
#UOSID=`get_o_asm_sid $dataDir $guser`

UOSID=`get_o_asm_sid $dataDir $guser`
if [ -z "$UOSID" ]; then
   logerrormsg EM_MISSING_SUBDIR $dataDir
   exit 1
fi

osid=$UOSID

export ORACLE_BASE=
export ORACLE_HOME=$oracleHome
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
export ORACLE_SID=$nsid

n=1
while [ "$n" -le "$undocnta" ]; do
        echo "create undo tablespace UNDOTBS_CL$n datafile '$dataDir1/$osid/datafile/undotbs_cl$n.dbf' size 500m autoextend on maxsize unlimited;" >> $openDatabase1
        n=$(($n + 1));
done

echo "alter system set cluster_database=TRUE scope=spfile sid='*';" >> $openDatabase1
echo "shutdown immediate;" >> $openDatabase1
echo "exit" >> $openDatabase1

if [ "$nonid" != "true" ]; then
if [ ! -f "$dbfsPath" ]; then
   roTBS='NONE'
else
   roTBS=`cat $dbfsPath`
fi

roCTBS=''
for i in $(echo $roTBS | tr "," "\n")
do
    echo "$i"
    if [ -z "$roCTBS" ]; then
	roCTBS="'''"${i}"'''"
    else
	roCTBS=${roCTBS}"\n'''"${i}"'''"
    fi
done

echo "$roCTBS"

ctbs_input=''
echo "$roCTBS" > roCTBS_output.txt
ctbs_input=`cat roCTBS_output.txt`
echo "$ctbs_input"

runnidsql="runnidrotbs"_$nsid".sql"
if [ -e "$runnidsql" ]; then
rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname ONLINE" >> $runnidsql
        echo "@nidROTBS.sql $readtabname 'READ WRITE'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql

su $osuser -c "$sqlplus / as sysdba " << EOF
@$runnidsql
shutdown immediate;
startup mount;
exit;
EOF

nidlog="nid_"$nsid".log"
su $osuser -c "$ORACLE_HOME/bin/nid target=/ LOGFILE=$nidlog "

if [ ! -f "$runnidsql" ]; then
 rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname 'READ ONLY'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql

su $osuser -c "$sqlplus / as sysdba " << EOF
startup mount;
alter database open resetlogs;
@$runnidsql
exit;
EOF
fi

change_own $osuser $openDatabase1
change_own $osuser $tabfilelist

if [ ! -f "roCTBS_output.txt" ]; then
 rm -f roCTBS_output.txt
fi

export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openDatabase1 " >> $openDBlog
retval=$?

if [ $retval -gt 0 ]; then
 logerrormsg EM_DB_OPEN_POST_UNDO_OP
 exit 1;
fi

if [ -e $dbrecover ]; then
   echo "removing $dbrecover "
  #rm -f $dbrecover
fi

if [ -e $recoverDatabase ]; then
   echo "removing $recoverDatabase "
   rm -f $recoverDatabase
fi

if [ -e $openDatabase ]; then
   echo "removing $openDatabase "
   rm -f $openDatabase
fi
if [ -e $openDatabase1 ]; then
   echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi
if [ -e $openTmpDatabase ]; then
   echo "removing $openTmpDatabase "
   rm -f $openTmpDatabase
fi
if [ -e $openFullDatabase ]; then
   echo "removing $openFullDatabase "
   rm -f $openFullDatabase
fi

if [ -e $addUnnamed ]; then
   echo "removing $addUnnamed "
   rm -f $addUnnamed
fi
if [ -e $verifyDatabase ]; then
   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi

if [ -e $openDBlog ]; then
   echo "removing $openDBlog "
  # rm -f $openDBlog
fi

if [ -e $tabfilelist ]; then
   echo "removing $tabfilelist "
   rm -f $tabfilelist
fi

if [ -e $createRedolog ]; then
  # echo "removing $createRedolog "
   rm -f $createRedolog
fi

nsid1=$nsid"1"
orapw1=$ORACLE_HOME"/dbs/orapw"$nsid1

if [ -e $orapw1 ]; then
   rm -f $orapw1
fi

set +x
syspwd="change_on_install"
if [ ! -z $ouser ] && [ "$ouser" = "sys" ] && [ ! -z $opass ]; then
   syspwd=$opass
fi

su $osuser -c "$ORACLE_HOME/bin/orapwd file=$orapw1 password=$syspwd entries=5"
set -x

# Copy pfile to other nodes
./copyPfileToNodes.sh $oracleHome $nsid $BASEDIR $osuser $orapw1

nsid1=$nsid"1"
export ORACLE_SID=$nsid1
#archivelog mode
su $osuser -c "$sqlplus / as sysdba " << EOF
startup mount;
alter database archivelog;
shutdown immediate;
exit;
EOF

su $osuser -c "srvctl start database -d $nsid"
retval=$?

opencnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ $retval -gt 0 ]; then
 logerrormsg EM_DB_OPEN_POST_PWD_FILE_OP
 exit 1;
elif [ $opencnt -eq 0 ]; then
 logerrormsg EM_DB_OPEN_POST_PWD_FILE_OP
 exit 1;
fi

rcount=0
while true
do
{
ponum=0
ponum=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feedback off
select count(*) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status = 'NEEDS RECOVERY';
exit;
EOF1`

if [ "$ponum" -gt "0" ]; then
undorollofflist=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select '*._offline_rollback_segments='||ltrim(rtrim(listagg(to_char(SEGMENT_NAME),',') WITHIN GROUP (ORDER BY SEGMENT_NAME ))) from dba_rollback_segs where status = 'PARTLY AVAILABLE' or status = 'NEEDS RECOVERY';
exit;
EOF1`

ttbs="*.undo_tablespace=UNDOTBS_CL1"
sed '/undo_tablespace/d' $orarollbacktemp > $temporafile
mv $temporafile $orarollbacktemp
sed '/offline_rollback_segments/d' $orarollbacktemp > $temporafile
mv $temporafile $orarollbacktemp
echo $ttbs >> $orarollbacktemp
echo $undorollofflist >> $orarollbacktemp
chmod 755 $orarollbacktemp
echo "shutdown immediate;" > $openTmpDatabase1
echo "startup force restrict pfile='$orarollbacktemp'" >> $openTmpDatabase1
echo "alter system set undo_tablespace=UNDOTBS_CL1;" >> $openTmpDatabase1

while read line
do
    set -- $line
    undoname=$1
        echo "outside if $undoname"
        if [ "x$undoname" != "x" ]; then
        echo "inside if $undoname"
        echo "drop tablespace $undoname including contents and datafiles;" >> $openTmpDatabase1
        fi
done < $undofilelist

echo "shutdown immediate;" >> $openTmpDatabase1
echo "startup;" >> $openTmpDatabase1
echo "exit" >> $openTmpDatabase1

chmod 755 $openTmpDatabase1
export ORACLE_SID=$nsid
su $osuser -c "sqlplus / as sysdba @$openTmpDatabase1 " >> $openDBlog
retval=$?
rcount=`expr $rcount + 1`
if [ "$rcount" -gt "3" ]; then
 break;
fi
sleep 10;
else
#exit 0;
  break;
fi
}
done

export ORACLE_SID=$nsid1

su $osuser -c "$sqlplus / as sysdba " << EOF3
drop tablespace TEMP_CL including contents and datafiles;
drop tablespace UNDOTBS_DEF including contents and datafiles;
exit;
EOF3

#grant user sysdba privilege
if [ ! -z $ouser ] && [ "$ouser" != "sys" ]; then
su $osuser -c "$sqlplus / as sysdba " << EOF
grant sysdba to $ouser;
exit;
EOF
fi

if [ "$pdbcnt" -gt "0" ]; then
su $osuser -c "$sqlplus / as sysdba " << EOF
ALTER pluggable database all open instances=all;
alter pluggable database all save state instances=all;
exit;
EOF
fi

opencnt=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ $retval -gt 0 ]; then
 exit 1;
elif [ $opencnt -eq 0 ]; then
 exit 1;
fi

if [ "$retval" -eq "0" ]; then
su $osuser -c "sqlplus / as sysdba " << EOF
alter database enable block change tracking;
purge recyclebin;
purge dba_recyclebin;
exit;
EOF
fi

if [ $pdbcnt -gt 0 ]; then
pdbname=`su $osuser -c "sqlplus -S / as sysdba " << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select ltrim(rtrim(listagg(to_char(name),',') WITHIN GROUP (ORDER BY name))) from v\$pdbs where name not like 'PDB%SEED';
exit;
EOF1`

for x in $(echo $pdbname | tr "," "\n")
do
su $osuser -c "sqlplus / as sysdba " << EOF
@tmpPDB.sql "$x" "$dataDir1"
exit;
EOF
done
fi

echo "set pagesize 100" >> $verifyDatabase
echo "set linesize 100" >> $verifyDatabase
echo "set echo off" >> $verifyDatabase
echo "set feedback off" >> $verifyDatabase
echo "set wrap on" >> $verifyDatabase
echo "col tablespace_name format a15" >> $verifyDatabase
echo "col file_name format a75" >> $verifyDatabase
echo "select distinct tablespace_name, file_name from dba_data_files order by 1;" >> $verifyDatabase
echo "select owner,count(*) from dba_tables group by owner order by 1;" >> $verifyDatabase
echo "select dbid, name, open_mode from v\$database;" >> $verifyDatabase
echo "select sum(bytes)/(1024*1024*1024) \"dbSize (GB)\" from dba_data_files;" >> $verifyDatabase
echo "select * from v\$instance;" >> $verifyDatabase
echo "exit" >> $verifyDatabase

change_own $osuser $verifyDatabase
fi

setdb="setdb_"$nsid
nsid1=$nsid"1"
echo "# Modify the following variables based on the environment" > $setdb
echo "export ORACLE_SID=$nsid1" >> $setdb
echo "export ORACLE_HOME=$ORACLE_HOME" >> $setdb
echo " " >> $setdb
echo "export PATH=$ORACLE_HOME/bin:$PATH" >> $setdb
echo "#END OF FILE" >> $setdb
change_own $osuser $setdb

cp $setdb ${scriptsRoot}/oracle/oracleclone/

export ORACLE_SID=$nsid1

if [ "$recoverdb" = "true" ]; then
su $osuser -c "$sqlplus / as sysdba @$verifyDatabase "
fi
if [ -e $verifyDatabase ]; then
   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi

otab=$nsid":"$ORACLE_HOME":N"
grep "$otab" /etc/oratab >/dev/null 2>&1 || echo "$otab" >> /etc/oratab

nsid1=$nsid"1"
otab=$nsid1":"$ORACLE_HOME":Y"
echo "$otab" >> /etc/oratab

if [ -e $pglist ]; then
   echo "removing $pglist"
   rm -f $pglist
fi
if [ -e $undofilelist ]; then
   echo "removing $undofilelist"
   rm -f $undofilelist
fi
if [ -e $openTmpDatabase1 ]; then
   echo "removing $openTmpDatabase1 "
   rm -f $openTmpDatabase1
fi

exit 0
fi
